package entity;

import database.DatabaseHelper;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.time.ZoneOffset;
import java.util.ArrayList;
import java.util.Date;

public class Score {

    private String sec_id;
    private String stu_id;
    private double usual;
    private double midterm;
    private double experiment;
    private double terminal;
    private double total;
    private LocalDateTime timeStamp;

    public double getUsual() {
        return usual;
    }

    public double getMidterm() {
        return midterm;
    }

    public double getExperiment() {
        return experiment;
    }

    public double getTerminal() {
        return terminal;
    }

    public double getTotal() {
        return total;
    }

    public LocalDateTime getTimeStamp() {
        return timeStamp;
    }

    private Score(String sec_id, String stu_id, double usual, double midterm, double experiment, double terminal, double total, LocalDateTime timeStamp) {
        this.sec_id = sec_id;
        this.stu_id = stu_id;
        this.usual = usual;
        this.midterm = midterm;
        this.experiment = experiment;
        this.terminal = terminal;
        this.total = total;
        this.timeStamp = timeStamp;
    }

    /* C */
    public static boolean create(DatabaseHelper db, Section sec, Student stu) throws SQLException {
        if (!sec.hasStudent(db, stu))
            return false;
        String update = "insert into score(sec_id,stu_id) value (?,?)";
        return db.update(update, sec.getId(), stu.getId()) > 0;
    }

    /* R */
    public static ArrayList<Score> findWhere(DatabaseHelper db, String condition, Object... params) throws SQLException {
        String query = "select sec_id,stu_id,usual,midterm,experiment,terminal,total,time_stamp from score where "+condition;
        ResultSet rs = db.query(query, params);
        ArrayList<Score> scores = new ArrayList<>();
        while (rs.next()) {
            scores.add(new Score(rs.getString(1), rs.getString(2), rs.getDouble(3), rs.getDouble(4), rs.getDouble(5), rs.getDouble(6), rs.getDouble(7), rs.getTimestamp(8).toLocalDateTime()));
        }
        rs.close();
        return scores;
    }

    public static Score find(DatabaseHelper db, Section sec, Student stu) throws SQLException {
        ArrayList<Score> scores = findWhere(db, "sec_id = ? and stu_id = ?", sec.getId(), stu.getId());
        return scores.isEmpty() ? null : scores.get(0);
    }

    public Section getSection(DatabaseHelper db) throws SQLException {
        return Section.find(db, sec_id);
    }

    public Student getStudent(DatabaseHelper db) throws SQLException {
        return Student.find(db, stu_id);
    }

    public boolean refresh(DatabaseHelper db) throws SQLException {
        Score score = find(db, getSection(db), getStudent(db));
        if (score == null)
            return false;
        usual = score.usual;
        midterm = score.midterm;
        experiment = score.experiment;
        terminal = score.terminal;
        total = score.total;
        timeStamp = score.timeStamp;
        return true;
    }

    /* U */
    public boolean setAttribute(DatabaseHelper db, String attribute, Object value) throws SQLException {
        String update = "update score set "+attribute+" = ? where sec_id = ? and stu_id = ?";
        int affectedRows = db.update(update, value, sec_id, stu_id);
        refresh(db);
        return affectedRows > 0;
    }

    public boolean setUsual(DatabaseHelper db, double usual) throws SQLException {
        return setAttribute(db, "usual", usual);
    }

    public boolean setMidterm(DatabaseHelper db, double midterm) throws SQLException {
        return setAttribute(db, "midterm", midterm);
    }

    public boolean setExperiment(DatabaseHelper db, double experiment) throws SQLException {
        return setAttribute(db, "experiment", experiment);
    }

    public boolean setTerminal(DatabaseHelper db, double terminal) throws SQLException {
        return setAttribute(db, "terminal", terminal);
    }

    public boolean setTotal(DatabaseHelper db, double total) throws SQLException {
        return setAttribute(db, "total", total);
    }

    public boolean setTimeStamp(DatabaseHelper db, LocalDateTime timeStamp) throws SQLException {
        return setAttribute(db, "time_stamp", Date.from(timeStamp.atZone(ZoneId.systemDefault()).toInstant()));
    }

    /* D */
    public boolean delete(DatabaseHelper db) throws SQLException {
        return db.update("delete from score where sec_id = ? and stu_id = ?", sec_id, stu_id) > 0;
    }

}
